"""
Author : 张程东
File : check_score.py 
"""
import pandas as pd
import numpy as np
import os
import re




def get_exist(class_id):
    regex="(.*?)(1+3)"
    if re.search(regex,class_id):
        return class_id.replace("(1+3)","")
    else:
        return class_id

#读取一位学生的信息
def student_info(path):
    s_info=pd.read_excel(path,header=None,dtype=object)

    for i in s_info.columns:
        if s_info[i].isnull().all():
            s_info.drop(columns=i,axis=1,inplace=True)
    student_class_id = get_exist(s_info.iloc[1, 7])
    info = {
        'student_name': s_info.iloc[0, 3],
        'student_id': s_info.iloc[0, 1],
        'student_class_id': student_class_id
    }

    student_data=pd.DataFrame(data=info,index=[0])
    student_data['flag']="检查表"
    return student_data


#读取教务处的总表数据
def get_jwch_info(path):
    jwch_data_sheet=pd.read_excel(path,sheet_name=None)
    jwch_all_data=pd.DataFrame(columns=["student_name","student_id","student_class_id"])
    for i in jwch_data_sheet:
        jwch_data=pd.read_excel(path,sheet_name=i,dtype=object)
        print(jwch_data.columns)
        if set(['姓名','学号','班级']).issubset(set(jwch_data.columns)):
            # jwch_data = pd.read_excel(path, sheet_name=i,converters={"学号":str})
            jwch_data=jwch_data.loc[:,['姓名','学号','班级']]
            jwch_data.rename(columns={"姓名":"student_name","学号":"student_id","班级":"student_class_id"},inplace=True)
            #将数据进行去空
            jwch_data['student_name']=jwch_data['student_name'].astype(str).apply(lambda x:x.replace(" ",""))
            jwch_data['student_id']=jwch_data['student_id'].astype(str).apply(lambda x:x.replace(" ",""))
            jwch_data['student_class_id']=jwch_data['student_class_id'].astype(str).apply(lambda x:x.replace(" ",""))
            jwch_all_data=pd.concat([jwch_all_data,jwch_data],ignore_index=True,axis=0)
        if set(['姓名','学号','班号']).issubset(set(jwch_data.columns)):
            # jwch_data = pd.read_excel(path, sheet_name=i,converters={"学号":str})
            jwch_data=jwch_data.loc[:,['姓名','学号','班号']]
            print("-"*20)
            jwch_data.rename(columns={"姓名":"student_name","学号":"student_id","班号":"student_class_id"},inplace=True)
            #将数据进行去空
            jwch_data['student_name']=jwch_data['student_name'].astype(str).apply(lambda x:x.replace(" ",""))
            jwch_data['student_id']=jwch_data['student_id'].astype(str).apply(lambda x:x.replace(" ",""))
            jwch_data['student_class_id']=jwch_data['student_class_id'].astype(str).apply(lambda x:x.replace(" ",""))
            jwch_all_data=pd.concat([jwch_all_data,jwch_data],ignore_index=True,axis=0)
    jwch_all_data['flag']="来自jwch"
    return jwch_all_data


#读取表二的总数据
def get_jx_info(path):
    jx_data_sheet=pd.read_excel(path,sheet_name=None)
    jx_all_data=pd.DataFrame(columns=["student_name","student_id","student_class_id"])
    for i in jx_data_sheet:
        jx_data=pd.read_excel(path,sheet_name=i,dtype=object)
        if set(['题名','学号','班级']).issubset(set(jx_data.columns)):
            # jx_data = pd.read_excel(path, sheet_name=i,converters={"学号":str})
            jx_data=jx_data.loc[:,['题名','学号','班级']]
            jx_data.rename(columns={"题名":"student_name","学号":"student_id","班级":"student_class_id"},inplace=True)
            #删除空数据所在的行
            jx_data.dropna(axis=0,inplace=True)
            #去空
            jx_data['student_name']=jx_data['student_name'].astype(str).apply(lambda x:x.replace(" ",""))
            jx_data['student_id']=jx_data['student_id'].astype(str).apply(lambda x:x.replace(" ",""))
            jx_data['student_class_id']=jx_data['student_class_id'].astype(str).apply(lambda x:x.replace(" ",""))
            jx_all_data=pd.concat([jx_all_data,jx_data],axis=0,ignore_index=True)
        if set(['题名','学号','班号']).issubset(set(jx_data.columns)):
            # jx_data = pd.read_excel(path, sheet_name=i,converters={"学号":str})
            jx_data=jx_data.loc[:,['题名','学号','班号']]
            jx_data.rename(columns={"题名":"student_name","学号":"student_id","班号":"student_class_id"},inplace=True)
            #删除空数据所在的行
            jx_data.dropna(axis=0,inplace=True)
            #去空
            jx_data['student_name']=jx_data['student_name'].astype(str).apply(lambda x:x.replace(" ",""))
            jx_data['student_id']=jx_data['student_id'].astype(str).apply(lambda x:x.replace(" ",""))
            jx_data['student_class_id']=jx_data['student_class_id'].astype(str).apply(lambda x:x.replace(" ",""))
            jx_all_data=pd.concat([jx_all_data,jx_data],axis=0,ignore_index=True)
    jx_all_data['flag']='来自jx表'
    return jx_all_data

#检查数据
def check_data(full_data,check_data):
    student_id=check_data.loc[0,'student_id']
    #根据学生的学号进行查询
    student_full=full_data.loc[full_data['student_id']==student_id,:]
    #将两个数据进行合并
    data=pd.concat([student_full,check_data],axis=0,ignore_index=True)
    #将数据表进行去重
    data.drop_duplicates(subset=['student_name', 'student_id', 'student_class_id'],keep=False,inplace=True)
    return data

#错误数据的保存
def save_error(data,path):
    #判断表内是否为空
    print(data)
    if not data.empty:
        data.to_excel(path,index=None)


if __name__=="__main__":

    student_info=student_info(r"H:\infomation\mission2\成绩\2012全日制成绩\2012\2012-JX14-0236\2012-JX14-0241-003.xlsx")
    data1=get_jwch_info(r"H:\infomation\mission2\成绩\1-2012教务处.xlsx")
    data2=get_jx_info(r"H:\infomation\mission2\成绩\2-2012-JX14(成绩).xlsx")
    error1=check_data(data1,student_info)
    error2=check_data(data2,student_info)
    #将数据进行保存
    save_error(error1,"./jwch.xlsx")
    save_error(error2,"./jx.xlsx")








